***********************************************************************************
* Merging NEW-OWES-ONET with Census 2010 and IPUMS 2010 codes
* last modified 12/21/2022
*
* Note: merge occupation codes
*
***********************************************************************************


clear
capture log close 
set more off

***************
* Directories *
***************

cd "/Users/"

global dofile			=	"./Code/NEW-OEWS"
global matrix_data		=   "./Data/National Employment Matrix"
global oews_data		= 	"./Data/OEWS/all_excel"
global onet_data		= 	"./OccLink/ONET/ONET data/merged"
global xwalk 			=	"./Data/Crosswalk"
global results 			=	"./Results"
global temp				= 	"./Temp"



****************************
* Import Census 2010 Codes *
****************************

/* 2010 SOC code */
import excel "${xwalk}/ONET_SOC_Code_Title_Cen2010-Dec21_2022.xlsx", sheet("Combined") cellrange(A3:BL1671) case(lower) firstrow clear

keep soccode soctitle bk bl

rename soccode soc
rename bk cen2010
rename bl cen2010title

duplicates drop soc cen2010, force

save "${temp}/crosswalk_soc2010_cen2010.dta", replace


use "${temp}/oews_matrix_ooh_onet98-20combined.dta", replace

/* 1998 SOC codes are not harmonized*/
drop if inrange(year, 1998, 1999)

merge m:1 soc using "${temp}/crosswalk_soc2010_cen2010", keep(1 3) nogenerate

save "${temp}/oews_matrix_ooh_onet2000-2020_soc.dta", replace


****************************
* Import IPUMS 2010 Codes *
****************************

/* 2010 Census/IPUMS codes */
import excel "${xwalk}/Cen2010_IPUMS_code.xlsx", case(lower) firstrow clear

rename censuscode2010 cen2010
rename ipums_code_2010 ipums2010
rename ipums_2010_valuelabel ipums_title

keep cen2010 ipums2010 ipums_title

save "${temp}/crosswalk_cen2010_ipums.dta", replace


use "${temp}/oews_matrix_ooh_onet2000-2020_soc.dta", replace

destring cen2010, replace

merge m:1 cen2010 using "${temp}/crosswalk_cen2010_ipums", keep(1 3) nogenerate


save "${temp}/oews_matrix_ooh_onet2000-2020_soc_ipums.dta", replace

export delimited using "${temp}/oews_matrix_ooh_onet2000-2020_soc_ipums", replace

export excel using "${temp}/oews_matrix_ooh_onet2000-2020_soc_ipums.xlsx", firstrow(variables) replace
